問題描述
SQL Server ‑ 分組、擁有和計數 (SQL Server ‑ Group by, having and count in a mix)
I have a database with a long list of records. Most of the columns have foreign keys to other tables.
Example:
ID SectorId BranchId
‑‑ ‑‑‑‑‑‑‑‑ ‑‑‑‑‑‑‑‑
5 3 5
And then I will have a table with sectors, branches ect.
My issue:
I want to know how many records which has sector 1, 2, 3 ... n. So what I want is a group by Sector and then some count(*) which will tell me how many there is of each.
Expected output
So for instance, if I have 20 records the result might look like this:
SectorId Count
‑‑‑‑‑‑‑‑ ‑‑‑‑‑
1 3
2 10
3 4
4 6
My attempts so far
I do not normally work a lot with databases and I have been trying to solve this for 1.5 hours. I have tried something like this:
SELECT COUNT(*)
FROM Records r
GROUP BY r.Sector
WHERE r.Date BETWEEN '2011‑01‑01' AND '2011‑12‑31'
But... errors and problems all over!
I would really appreciate some help. I do know this is probably very simple.
Thanks!
‑‑‑‑‑
參考解法
方法 1:
The sequence of your query is not correct; it should be like this: ‑
SELECT COUNT(*)
FROM Records r
WHERE r.Date BETWEEN '2011‑01‑01' AND '2011‑12‑31'
GROUP BY r.Sector
The output will be only counts i.e.
count
‑‑‑‑‑
3
10
4
6
If you want to fetch both sector and count then you need to modify the query a little
SELECT r.Sector, COUNT(*) as Count
FROM Records r
WHERE r.Date BETWEEN '2011‑01‑01' AND '2011‑12‑31'
GROUP BY r.Sector
The output will be like this: ‑
Sector Count
‑‑‑‑‑‑ ‑‑‑‑‑
1 3
2 10
3 4
3 6
方法 2:
Your query was partially right,But it needs some modification.
If I write this way:‑
SELECT r.SectorID,COUNT(*) AS count
FROM Records r
WHERE r.Date BETWEEN '2011‑01‑01' AND '2011‑12‑31'
GROUP BY r.SectorID
Then output will be:‑
SectorID Count
1 3
2 10
3 4
4 6
(by Lars Holdgaard、Waqas Raja、Bhaskar saha)